/*该存储过程允许仿照入库倒冲进行材料出库
存储过程的参数分别是外部系统ID、创建人、生产订单行ID、扣料套数，
注意问题
1.该存储过程只包含出库部分，并不包含入库部分。入库部分还是按原过程执行
2.该程序传入的iQuantity为“入库套数”，即按套数对子件的出库数量进行消耗
3.该程序不检查现存量。
4.该程序不检查本次发料数量是否大于剩余可发数量，但是如果某个子件已经发完，则不再执行，其它未发的行，继续执行
5.该程序只出库订单子件中标记了“入库倒冲”的行
6.该程序一批订单倒冲中如果有多个仓库，则拆分成多个仓库建单
7.如果不想分别调用入库与扣料，则可以把本程序挂入到SaveStockIn4MO存储过程中执行即可，
	但是不建议这样做，因为两个程序都是独立的事务
8.务必进行测试，包括订单子件混合仓库，混合发料模式（既有领料又有倒冲），
	非整数的用量测试，领料与自动扣料混合测试，删单、改单、反复反审单测试、
	可用量表观察、流水表观察、成本计算测试，等等
*/


CREATE OR ALTER PROCEDURE [dbo].[SaveStockOut4Backflush]
@cCode1 int,@cMaker varchar(100),@ModId int,@iQuantity decimal(30,10),@msg varchar(500) output
AS
BEGIN
DECLARE @cCode VARCHAR(100)
SET @cCode = CONVERT(VARCHAR(100), @cCode1 + 1000000) 
SET @cCode = CONVERT(VARCHAR(6),GETDATE(),12) + @cCode  --加上年月日，比如240625 ，如果把VARCHAR(6)改成VARCHAR（4），则只取2406
SET @cCode = 'MD'+@cCode   --加上前缀
--此时@cCode应该是MD202425100012345 ,如果你的AUTOID = 12345的话

DECLARE @ACCID VARCHAR(100)
DECLARE @FATHERID VARCHAR(100)
DECLARE @CHILDID VARCHAR(100)
DECLARE @TODAY DATETIME
DECLARE @NOW DATETIME
DECLARE @cDepCode VARCHAR(100)
DECLARE @iMQuantity decimal(30,10)
DECLARE @iNQuantity decimal(30,10)
DECLARE @cPsPcode VARCHAR(100)
DECLARE @cMoPode VARCHAR(100)
DECLARE @iMoSeq varchar(20)
DECLARE @iPeSoDid INT
DECLARE @iPeSoType varchar(20)
DECLARE @iPeSoSeq INT
DECLARE @iSoType INT
DECLARE @cPeSoCode varchar(100)
DECLARE @MOID INT
DECLARE @APPSTATUS INT
DECLARE @COUNTS INT
DECLARE @WHCOUNTS INT
SET @ACCID = '001' --真实环境应当修改这个
SELECT @TODAY = CONVERT(DATETIME, CONVERT(CHAR(20), GETDATE(), 110))
SELECT @NOW = GETDATE()
IF EXISTS(SELECT 1 FROM RdRecord11 WHERE cCode = @cCode+'1')
BEGIN
SET @MSG = '单号重复'
RETURN 1 
END

SELECT @cDepCode=A.MDeptCode , @cPsPcode = A.InvCode,
@cMoPode = C.MoCode,@iMoSeq = a.SortSeq ,
@iPeSoType = '7',@cPeSoCode=C.MOCODE,@iPeSoSeq=a.sortseq,@iMQuantity = a.qty,@iSoType=0,@moid=c.moid,
@APPSTATUS= Status 
FROM MOM_ORDERDETAIL A LEFT JOIN mom_order C ON C.MoId = A.MoId WHERE A.MoDid = @MoDid

IF (@APPSTATUS != 3)
BEGIN
SET @MSG = '订单状态不是已审核'
RETURN 4
END 

IF NOT EXISTS(SELECT 1 FROM  UFSystem.dbo.UA_Account WHERE cAcc_Id = @ACCID)
BEGIN
SET @MSG = 'ACCid配置错误'
RETURN 9 
END 

IF EXISTS (SELECT * FROM MOM_MOALLOCATE WHERE MODID = @MoDid AND WIPTYPE = 1 AND WHCODE IS NULL)
BEGIN
 SET @MSG = '虽然存在倒冲行，但没有设置倒冲仓库'
 RETURN 11
END

SELECT @COUNTS = COUNT(*) FROM MOM_MOALLOCATE WHERE MODID = @MoDid AND WIPTYPE = 1 AND ISSQTY < QTY 
IF @COUNTS = 0
BEGIN
 SET @MSG = '不存在倒冲的项'
 RETURN 11
END
SELECT @WHCOUNTS = COUNT(*) FROM (SELECT DISTINCT WHCODE FROM MOM_MOALLOCATE WHERE MODID = @MoDid AND WIPTYPE = 1 AND ISSQTY < QTY )T

CREATE TABLE #T1 (
    ID INT,
    WH VARCHAR(100)
);



BEGIN TRANSACTION
--处理PK
SELECT @FATHERID=iFatherId,@CHILDID=iChildId
	FROM UFSystem.dbo.UA_Identity WITH(NOLOCK) WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
UPDATE UFSystem.dbo.UA_Identity SET iFatherId = @FATHERID + @WHCOUNTS,iChildId = @CHILDID + @COUNTS WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';

INSERT INTO #T1(ID,WH) 
	SELECT @FATHERID+1000000000+ROW_NUMBER() OVER (ORDER BY WHCODE),WHCODE 
	FROM MOM_MOALLOCATE 
	WHERE MODID = @MoDid AND WIPTYPE = 1 AND ISSQTY < QTY 
	GROUP BY WHCODE  

--写入材料出库单
INSERT INTO RdRecord11(ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode,cMaker,cHandler,dVeriDate,VT_ID,dnmaketime,dnverifytime,cDepCode,iMQuantity,cMPoCode,cPsPcode,bOMFirst,bmotran,bHYVouch ,iproorderid)
SELECT ID,0,'11','领料','生产订单',WH,@TODAY,@cCode+CONVERT(VARCHAR(10),ROW_NUMBER() OVER (ORDER BY ID)),@cMaker,@cMaker,@TODAY,65,@NOW,@NOW,@cDepCode,@iMQuantity,@cMoPode,@cPsPcode,0,0,0,@moid
	FROM #T1 
IF @@ROWCOUNT != @WHCOUNTS
BEGIN
SET @MSG = 'SQL执行INSERT INTO RdRecord11错误'
ROLLBACK TRANSACTION
RETURN 101
END
--写入出库单明细
INSERT INTO RdRecords11(AutoID,ID,cInvCode,iQuantity,irowno,bCosting,iNQuantity,iMPoIds,cmocode,invcode,imoseq,iopseq,ipesotype,cpesocode,ipesoseq,iExpiratDateCalcu,iorderdid,isotype,ipesodid) 
SELECT @CHILDID+1000000000+ROW_NUMBER() OVER (ORDER BY ALLOCATEID),ID,INVCODE,BASEQTYN/BASEQTYD * (1+compscrap/100) * @iQuantity,ROW_NUMBER() OVER (ORDER BY ALLOCATEID),bInCost,BASEQTYN/BASEQTYD * @iQuantity,
AllocateId,@cMoPode,@cPsPcode,@iMoSeq,OpSeq,@iPeSoType,@cPeSoCode,@iPeSoSeq,0,0,@isotype,@iPeSoDid 
FROM MOM_MOALLOCATE A LEFT JOIN WAREHOUSE B ON A.WHCODE = B.cWhCode LEFT JOIN #T1 C ON A.WHCODE = C.WH WHERE MODID = @MoDid AND WIPTYPE = 1 AND ISSQTY < QTY 
IF @@ROWCOUNT != @COUNTS
BEGIN
SET @MSG = 'SQL执行INSERT INTO RdRecords11错误'
ROLLBACK TRANSACTION
RETURN 102
END
--写入IA
INSERT INTO IA_ST_UnAccountVouch11(IDUN,IDSUN,cVouTypeUN,cBustypeUN)
	SELECT ID,AutoID,11,'领料' FROM RdRecords11 A WHERE ID IN (SELECT ID FROM #T1);

--更新仓库现存量，如果需要支持其他维度如批次维度等，需要在这里进行评估
UPDATE CurrentStock 
	SET CurrentStock.iQuantity = CurrentStock.iQuantity - BASEQTYN/BASEQTYD * @iQuantity 
	FROM CurrentStock LEFT JOIN MOM_MOALLOCATE 
		ON CurrentStock.CINVCODE = MOM_MOALLOCATE.InvCode AND CurrentStock.CWHCODE = MOM_MOALLOCATE.WhCode 
	WHERE MODID = @MoDid AND WIPTYPE = 1 AND ISSQTY < QTY 
	
IF @@ROWCOUNT != @COUNTS
BEGIN
SET @MSG = 'SQL执行UPDATE CurrentStock错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
ROLLBACK TRANSACTION
RETURN 103
END
--回写对应的生产订单发料记录
UPDATE mom_moallocate SET IssQty = ISNULL(IssQty,0) + BASEQTYN/BASEQTYD * @iQuantity,requisitionissqty=ISNULL(requisitionissqty,0) + BASEQTYN/BASEQTYD * @iQuantity WHERE MODID = @MoDid AND WIPTYPE = 1 AND ISSQTY < QTY
IF @@ROWCOUNT != @COUNTS
BEGIN
SET @MSG = 'SQL执行UPDATE mom_moallocate错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
ROLLBACK TRANSACTION
RETURN 105
END
--更谨慎的做法是对每个UPDATE行的结果进行
SET @msg = @cCode
COMMIT TRANSACTION
END